
using System;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.IO;
using System.Windows.Forms;
using System.Collections.Generic;

namespace Maniac
{
	
	
	public class DataTier
	{
		
		public DataTier()
		{
		}
		public static string ConnectionString = "Data Source="+Path.Combine(Application.StartupPath, "maniac3.s3db")+";Version=3";

        public static void DeleteSymbol(string Symbol)
        {
        }
        public static void DeleteAllData()
        {
            using (SQLiteConnection conn = new SQLiteConnection(DataTier.ConnectionString))
            {
                SQLiteCommand cmd = new SQLiteCommand(DataTier.DeleteDayAggregates_Query, conn);
                ExecuteCmd(cmd, conn);
                cmd = new SQLiteCommand(DataTier.DeleteHistoricalData_Query, conn);
                ExecuteCmd(cmd, conn);
                cmd = new SQLiteCommand(DataTier.DeleteSymbols_Query, conn);
                ExecuteCmd(cmd, conn);
                
            }
        }
        private static void ExecuteCmd(SQLiteCommand cmd, SQLiteConnection conn)
        {
            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
		public static int GetSymbolID(string Symbol)
		{
			string sql = "SELECT id FROM Symbols where Symbol = '" + Symbol +"';";
			SQLiteConnection cn = new SQLiteConnection(DataTier.ConnectionString);
			//if (cn.State == System.Data.ConnectionState.Closed)
                cn.Open();
			SQLiteCommand cmd = new SQLiteCommand();
			cmd.Connection = cn;
            cmd.CommandText = sql;
            
			int tviResult;
				
			DbDataReader SQLreader = cmd.ExecuteReader();
            if (SQLreader.Read())
            	tviResult = SQLreader.GetInt32(0);
			else
				tviResult = DataTier.AddSymbol(Symbol);
			
			cmd.Dispose();
			cn.Close();
			return tviResult;
		}
		public static bool IsSymbolValid(string Symbol)
		{
			string sql = "SELECT id FROM Symbols where Symbol = '" + Symbol +"';";
			SQLiteConnection cn = new SQLiteConnection(DataTier.ConnectionString);
			cn.Open();
			SQLiteCommand cmd = new SQLiteCommand();
			cmd.Connection = cn;
            cmd.CommandText = sql;
            
			bool tvbResult = false;
				
			DbDataReader SQLreader = cmd.ExecuteReader();
            if (SQLreader.Read())
            	tvbResult = true;
			
			cmd.Dispose();
			cn.Close();
			return tvbResult;
		}
		public static int AddSymbol(string Symbol)
		{
			int newSymbolID = 0;
    string sql =
        "INSERT OR IGNORE INTO Symbols (Symbol) VALUES (@Symbol); "
        + "SELECT id FROM Symbols WHERE Symbol = @Symbol;";
    using (SQLiteConnection conn = new SQLiteConnection(DataTier.ConnectionString))
    {
        SQLiteCommand cmd = new SQLiteCommand(sql, conn);
        cmd.Parameters.Add("@Symbol",DbType.String);
        cmd.Parameters["@Symbol"].Value = Symbol;
        try
        {
            conn.Open();
            newSymbolID = Convert.ToInt32(cmd.ExecuteScalar());
        }
        catch (Exception ex)
        {
            Console.WriteLine("AddSymbol: " + ex.Message);
        }
    }
    return (int)newSymbolID;

		}
		public static void SaveRawHistoricalData(string [] rows, int SymbolID)
		{
			SQLiteConnection cn = new SQLiteConnection(DataTier.ConnectionString);
            cn.Open();
            // always do multiple operations in SQLite in a transaction!
           SQLiteTransaction trans= cn.BeginTransaction();
                    
		
			for (int i = 1; i < rows.Length; i++)
                {
                    if (rows[i].Length < 1)
                        continue;
                    string[] rowValues = rows[i].Split(new char[] { ',' });
                    string[] dateparts = rowValues[0].Split(new char[] { '-' });


                    int tviYear = Convert.ToInt32(dateparts[0]);
                    int tviMonth = Convert.ToInt32(dateparts[1]);
                    int tviDay = Convert.ToInt32(dateparts[2]);
string sql = "INSERT OR IGNORE INTO HistoricalPrices (DATE,OPEN,HIGH,LOW,CLOSE,VOLUME,ADJCLOSE,SYMBOLID,PRIORCLOSE,DAY,MONTH,YEAR) VALUES(";
                    DateTime tvdDate = new DateTime(tviYear, tviMonth, tviDay);
				sql+="'"+tvdDate.ToString() + "',";
                float open = (float)Convert.ToDouble(rowValues[1]);
				sql+="'"+open+"',";
                float high = (float)Convert.ToDouble(rowValues[2]);
				sql+="'"+high+"',";
                float low = (float)Convert.ToDouble(rowValues[3]);
				sql+="'"+low+"',";
                float close = (float)Convert.ToDouble(rowValues[4]);
				sql+="'"+close+"',";
                double volume = Convert.ToDouble(rowValues[5]);
				sql+="'"+volume+"',";
                float adjclose = (float)Convert.ToDouble(rowValues[6]);
				sql+="'"+adjclose+"'," + SymbolID;
				if ((rows[i+1]!=null) && (rows[i+1].Length>0))
				{
					//I can obtain the prior close.
					string[] rowValues2 = rows[i+1].Split(new char[] { ',' });
					float priorclose = (float)Convert.ToDouble(rowValues2[4]); //yikes
					sql+=",'"+priorclose+"',";
				}
				else
					sql+= ",'0',";
				sql+="'"+tviDay+"','"+tviMonth+"','"+tviYear+"');";
                				
				 SQLiteCommand cmd = trans.Connection.CreateCommand();
            cmd.CommandText = sql;
            
            if (trans.Connection.State == System.Data.ConnectionState.Closed)
                trans.Connection.Open();
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            }
			 trans.Commit();  //using a transaction for SQLite speeds up multi-inserts bigtime!
            cn.Close();
		}
		public static void GetHistoricalPrices(List<HistoricalPrice> HistoricalPrices, string Symbol)
		{
			HistoricalPrices.Clear();
			using (SQLiteConnection conn = new SQLiteConnection(DataTier.ConnectionString))
    		{
        		SQLiteCommand cmd = new SQLiteCommand(DataTier.GetHistoricalPrice_Query, conn);
				
        		cmd.Parameters.Add("@Symbol",DbType.String);
        		cmd.Parameters["@Symbol"].Value = Symbol;
				
		        try
				{
	            	conn.Open();
	            	DbDataReader SQLreader = cmd.ExecuteReader();
            		while (SQLreader.Read())
					{
						HistoricalPrice tvHist = new HistoricalPrice();

	                    tvHist.Year = SQLreader.GetInt32(12);
	                    tvHist.Month = SQLreader.GetInt32(11);
	                    tvHist.Day = SQLreader.GetInt32(10);
	
	                    tvHist.Date = new DateTime(tvHist.Year, tvHist.Month, tvHist.Day);
						tvHist.OpenPrice = SQLreader.GetDecimal(1);
	                    tvHist.HighPrice = SQLreader.GetDecimal(2);
	                    tvHist.LowPrice = SQLreader.GetDecimal(3);
	                    tvHist.ClosePrice = SQLreader.GetDecimal(4);
	                    tvHist.CloseAdjustedPrice = SQLreader.GetDecimal(6);
	                    tvHist.Volume = SQLreader.GetInt32(5);
						tvHist.PriorClosePrice = SQLreader.GetDecimal(9);						                                 
	                    tvHist.Symbol = Symbol;
						
						HistoricalPrices.Add(tvHist);
					}
					conn.Close();
	        	}
		        catch (Exception ex)
		        {
		            Console.WriteLine("GetHistoricalPrices: " + ex.Message);
				}
    		}
		}
		public static void GetHistoricalPrices(List<HistoricalPrice> HistoricalPrices, string Symbol, int Day, int Month)
		{
			HistoricalPrices.Clear();
			using (SQLiteConnection conn = new SQLiteConnection(DataTier.ConnectionString))
    		{
        		SQLiteCommand cmd = new SQLiteCommand(DataTier.GetHistoricalPriceDayMonth_Query, conn);
        		cmd.Parameters.Add("@Symbol",DbType.String);
        		cmd.Parameters["@Symbol"].Value = Symbol;
				cmd.Parameters.Add("@Day",DbType.Int32);
        		cmd.Parameters["@Day"].Value = Day;
				cmd.Parameters.Add("@Month",DbType.Int32);
        		cmd.Parameters["@Month"].Value = Month;
				
		        try
				{
	            	conn.Open();
	            	DbDataReader SQLreader = cmd.ExecuteReader();
            		while (SQLreader.Read())
					{
						HistoricalPrice tvHist = new HistoricalPrice();

	                    tvHist.Year = SQLreader.GetInt32(12);
	                    tvHist.Month = SQLreader.GetInt32(11);
	                    tvHist.Day = SQLreader.GetInt32(10);
	
	                    tvHist.Date = new DateTime(tvHist.Year, tvHist.Month, tvHist.Day);
						tvHist.OpenPrice = SQLreader.GetDecimal(1);
	                    tvHist.HighPrice = SQLreader.GetDecimal(2);
	                    tvHist.LowPrice = SQLreader.GetDecimal(3);
	                    tvHist.ClosePrice = SQLreader.GetDecimal(4);
	                    tvHist.CloseAdjustedPrice = SQLreader.GetDecimal(6);
	                    tvHist.Volume = SQLreader.GetInt32(5);
						tvHist.PriorClosePrice = SQLreader.GetDecimal(9);						                                 
	                    tvHist.Symbol = Symbol;
						
						HistoricalPrices.Add(tvHist);
					}
					conn.Close();
	        	}
		        catch (Exception ex)
		        {
		            Console.WriteLine(ex.Message);
				}
    		}
		}				
		private static string GetReportBasic(string Query)
		{
			string ReturnData="";
			DbDataReader SQLreader=null;
			
			using (SQLiteConnection conn = new SQLiteConnection(DataTier.ConnectionString))
    		{				
        		SQLiteCommand cmd = new SQLiteCommand(Query, conn);
				
		        try
				{
	            	conn.Open();
				
	            	SQLreader = cmd.ExecuteReader();					

					System.Text.StringBuilder sb = new System.Text.StringBuilder();
            		while (SQLreader.Read())
					{						
						for(int i=0;i<SQLreader.FieldCount;i++)
						{
							sb.Append(SQLreader.GetString(i));
							sb.Append("\t");
						}
						sb.Append("\n");
					}
					conn.Close();

					ReturnData = sb.ToString();
	        	}
		        catch (Exception ex)
		        {
		            Console.WriteLine(ex.Message);
				}
    		}
			return ReturnData;
		}
		public static string GetReportProbability(string Data, string SymbolList)
		{
			string ReturnData = "";
			string sQuery = DataTier.GetReportProbability_Query.Replace("@SymbolList", SymbolList);
			ReturnData = DataTier.GetReportBasic(sQuery);
			return Data + ReturnData;
		}
        public static string GetReport3Days(string Data, string SymbolList)
        {
            string ReturnData = "";
            string sQuery = DataTier.GetReport3Days_Query.Replace("@SymbolList", SymbolList);
            ReturnData = DataTier.GetReportBasic(sQuery);
            return Data + ReturnData;
        }
        public static string GetReport5Days(string Data, string SymbolList)
        {
            string ReturnData = "";
            string sQuery = DataTier.GetReportGain_Query.Replace("@SymbolList", SymbolList);
            ReturnData = DataTier.GetReportBasic(sQuery);
            return Data + ReturnData;
        }
        public static string GetReport1Month(string Data, string SymbolList)
        {
            string ReturnData = "";
            string sQuery = DataTier.GetReportGain_Query.Replace("@SymbolList", SymbolList);
            ReturnData = DataTier.GetReportBasic(sQuery);
            return Data + ReturnData;
        }
        public static string GetReport3Months(string Data, string SymbolList)
        {
            string ReturnData = "";
            string sQuery = DataTier.GetReportGain_Query.Replace("@SymbolList", SymbolList);
            ReturnData = DataTier.GetReportBasic(sQuery);
            return Data + ReturnData;
        }

		public static string GetReportGain(string Data, string SymbolList)
		{
			string ReturnData = "";
			string sQuery = DataTier.GetReportGain_Query.Replace("@SymbolList", SymbolList);
			ReturnData = DataTier.GetReportBasic(sQuery);
			return Data + ReturnData;
		}

		public static string GetReportGainPercent(string Data, string SymbolList)
		{
			string ReturnData = "";
			string sQuery = DataTier.GetReportGainPercent_Query.Replace("@SymbolList", SymbolList);
			ReturnData = DataTier.GetReportBasic(sQuery);
			return Data + ReturnData;
		}
		public static void GetDayAggregatesSortedByDate(List<DayAggregate> DayAggregates, string SymbolList, int Day, int Month, string SortOrder)
		{
			DayAggregates.Clear();
			using (SQLiteConnection conn = new SQLiteConnection(DataTier.ConnectionString))
    		{
				string sQuery = DataTier.GetScreenDay_Query.Replace("@SymbolList", SymbolList);
                sQuery = sQuery.Replace("@SortOrder", SortOrder);
        		SQLiteCommand cmd = new SQLiteCommand(sQuery, conn);
        		cmd.Parameters.Add("@SymbolList",DbType.AnsiString);
        		cmd.Parameters["@SymbolList"].Value = SymbolList;
				cmd.Parameters.Add("@Day",DbType.Int32);
        		cmd.Parameters["@Day"].Value = Day;
				cmd.Parameters.Add("@Month",DbType.Int32);
        		cmd.Parameters["@Month"].Value = Month;
                cmd.Parameters.Add("@SortOrder", DbType.StringFixedLength);
        		cmd.Parameters["@SortOrder"].Value = SortOrder;

		        try
				{
	            	conn.Open();
	            	DbDataReader SQLreader = cmd.ExecuteReader();
            		while (SQLreader.Read())
					{												
						DayAggregate tvdAgg = new DayAggregate();
						tvdAgg.AverageGainPercentPriorClose=SQLreader.GetDecimal(1);
						tvdAgg.RiseProbability=SQLreader.GetDecimal(3);
	                    tvdAgg.Symbol = SQLreader.GetString(0);
						tvdAgg.Day = Day;
						tvdAgg.Month=Month;
						
                    	DayAggregates.Add(tvdAgg);
					}
					conn.Close();
	        	}
		        catch (Exception ex)
		        {
		            Console.WriteLine(ex.Message);
				}
    		}
		}
		public static void GetDayAggregates(List<DayAggregate> DayAggregates, string Symbol)
		{
			DayAggregates.Clear();
			using (SQLiteConnection conn = new SQLiteConnection(DataTier.ConnectionString))
    		{
        		SQLiteCommand cmd = new SQLiteCommand(DataTier.GetDayAggregates_Query, conn);
        		cmd.Parameters.Add("@Symbol",DbType.String);
        		cmd.Parameters["@Symbol"].Value = Symbol;				
		        try
				{
	            	conn.Open();
	            	DbDataReader SQLreader = cmd.ExecuteReader();
            		while (SQLreader.Read())
					{
						DayAggregate tvdAgg = new DayAggregate();
						
						tvdAgg.AverageGain=SQLreader.GetDecimal(1);
						tvdAgg.AverageGainPercent=SQLreader.GetDecimal(2);
						tvdAgg.AverageGainPercentPriorClose=SQLreader.GetDecimal(3);
						tvdAgg.AverageGainPriorClose=SQLreader.GetDecimal(4);
						tvdAgg.AveragePrice=SQLreader.GetDecimal(5);
						tvdAgg.Day=SQLreader.GetInt32(6);						
						tvdAgg.MaxGain=SQLreader.GetDecimal(14);
						tvdAgg.MaxGainPercent=SQLreader.GetDecimal(16);
						tvdAgg.MaxLoss=SQLreader.GetDecimal(18);
						tvdAgg.MaxLossPercent=SQLreader.GetDecimal(20);
						tvdAgg.Month=SQLreader.GetInt32(22);
						tvdAgg.TotalFalling=SQLreader.GetInt32(27);
						tvdAgg.TotalRising=SQLreader.GetInt32(28);
	                    tvdAgg.Symbol = Symbol;
						
                    	DayAggregates.Add(tvdAgg);
						
						
					}
					conn.Close();
	        	}
		        catch (Exception ex)
		        {
		            Console.WriteLine(ex.Message);
				}
    		}
		}
		//what about taking a list of symbols, and loading the aggregates?
		
				public static void GetDayAggregates2(List<DayAggregate> DayAggregates, string Symbol, int Day, int Month)
		{
			//DayAggregates.Clear();
			using (SQLiteConnection conn = new SQLiteConnection(DataTier.ConnectionString))
    		{
        		SQLiteCommand cmd = new SQLiteCommand(DataTier.GetDayAggregatesDayMonth_Query, conn);
        		cmd.Parameters.Add("@Symbol",DbType.String);
        		cmd.Parameters["@Symbol"].Value = Symbol;
				cmd.Parameters.Add("@Day",DbType.Int32);
        		cmd.Parameters["@Day"].Value = Day;
				cmd.Parameters.Add("@Month",DbType.Int32);
        		cmd.Parameters["@Month"].Value = Month;
		        try
				{
	            	conn.Open();
	            	DbDataReader SQLreader = cmd.ExecuteReader();
            		while (SQLreader.Read())
					{
						DayAggregate tvdAgg = new DayAggregate();
						
						tvdAgg.AverageGain=SQLreader.GetDecimal(1);
						tvdAgg.AverageGainPercent=SQLreader.GetDecimal(2);
						tvdAgg.AverageGainPercentPriorClose=SQLreader.GetDecimal(3);
						tvdAgg.AverageGainPriorClose=SQLreader.GetDecimal(4);
						tvdAgg.AveragePrice=SQLreader.GetDecimal(5);
						tvdAgg.Day=SQLreader.GetInt32(6);
						//ignore computer values GainMedian, etc
						tvdAgg.MaxGain=SQLreader.GetDecimal(14);
						tvdAgg.MaxGainPercent=SQLreader.GetDecimal(16);
						tvdAgg.MaxLoss=SQLreader.GetDecimal(18);
						tvdAgg.MaxLossPercent=SQLreader.GetDecimal(20);
						tvdAgg.Month=SQLreader.GetInt32(22);
						tvdAgg.TotalFalling=SQLreader.GetInt32(27);
						tvdAgg.TotalRising=SQLreader.GetInt32(28);
	                    tvdAgg.Symbol = Symbol;
						
                    	DayAggregates.Add(tvdAgg);
						
						
					}
					conn.Close();
	        	}
		        catch (Exception ex)
		        {
		            Console.WriteLine(ex.Message);
				}
    		}
		}

		public static void ETLDayAggregates(string Symbol)
		{
		    using (SQLiteConnection conn = new SQLiteConnection(DataTier.ConnectionString))
    		{
        		SQLiteCommand cmd = new SQLiteCommand(DataTier.ETLDayAggregates_Query, conn);
        		cmd.Parameters.Add("@Symbol",DbType.String);
        		cmd.Parameters["@Symbol"].Value = Symbol;
				//cmd.CommandType = CommandType.StoredProcedure;
		        try
				{
	            	conn.Open();
	            	cmd.ExecuteNonQuery();
					conn.Close();
	        	}
		        catch (Exception ex)
		        {
		            Console.WriteLine(ex.Message);
				}
    		}
		}
		public static void ETLDayAggregateHistoricalPrice(string Symbol)
		{
		    using (SQLiteConnection conn = new SQLiteConnection(DataTier.ConnectionString))
    		{
        		SQLiteCommand cmd = new SQLiteCommand(DataTier.ETLDayAggregateHistoricalPrice_Query, conn);
        		cmd.Parameters.Add("@Symbol",DbType.String);
        		cmd.Parameters["@Symbol"].Value = Symbol;

		        try
				{
	            	conn.Open();
	            	cmd.ExecuteNonQuery();
					conn.Close();
	        	}
		        catch (Exception ex)
		        {
		            Console.WriteLine(ex.Message);
				}
    		}
		}
		public static string ETLDayAggregates_Query = @"
INSERT OR IGNORE INTO DayAggregates
select
NULL as id,
AVG(Close-Open) As 'AverageGain',
AVG((Close-Open)/Open*100) AS 'AverageGainPercent',
AVG((Close-PriorClose)/PriorClose*100) AS 'AverageGainPercentPriorClose',
AVG(Close-PriorClose) As 'AverageGainPriorClose',
AVG(Close) AS 'AveragePrice',
--SUBSTR(historicalprices.Date, 4, 2) AS 'Day',
historicalprices.Day AS 'Day',
--SELECT AVG((t.row-sub.a)*(t.row-sub.a)) as var from t, (SELECT AVG(row) AS a FROM t) AS sub
0 as GainMedian,
0 as GainPercentMedian,
0 as GainPercentStandardDeviation,
0 as GainPercentStandardDeviationDelta,
0 as GainPercentVariance,
0 as GainStandardDeviation,
0 as GainVariance,
Max(Close-Open) AS 'MaxGain',
0 as MaxGainDate,
MAX((Close-Open)/Open*100) AS 'MaxGainPercent',
0 as MaxGainPercentDate,
Min(Close-Open) AS 'MaxLoss',
0 as MaxLossDate,
Min((Close-Open)/Open*100) AS 'MaxLossPercent',
0 as MaxLossPercentDate,
--SUBSTR(historicalprices.Date, 0, 2) AS 'Month',
historicalprices.Month AS 'Month',
0 as MostRecentActualGain,
0 as MostRecentActualGainPercent,
0 as OrdinalPosition,
NULL as RiseProbability,
Count(NULLIF(((Close-PriorClose)<0),0)) AS TotalRising,
Count(NULLIF(((Close-PriorClose)>=0),0)) AS TotalFalling,
SymbolID

/*Min(Close-PriorClose) AS 'MaxLossPriorClose',
Min((Close-PriorClose)/PriorClose*100) AS 'MaxLossPercentPriorClose',


Min(Close) As LowestPrice,
Max(Close) AS HighestPrice,



Max(Close-PriorClose) AS 'MaxGainPriorClose',
MAX((Close-PriorClose)/PriorClose*100) AS 'MaxGainPercentPriorClose',



0 as riseprobability,
*/
--TODO








from historicalprices
inner join symbols on symbols.id = historicalprices.SymbolID
where symbols.Symbol = @Symbol
GROUP BY Month, Day, Symbolid";
		public static string ETLDayAggregateHistoricalPrice_Query = @"
INSERT OR IGNORE INTO DayAggregateHistoricalPrice
Select DayAggregates.id, HistoricalPrices.id from DayAggregates
inner join historicalprices on historicalprices.SymbolID = dayaggregates.symbolid
inner join symbols on symbols.id = dayaggregates.SymbolID
where symbols.Symbol = @Symbol   and
DayAggregates.Day = historicalprices.Day AND
DayAggregates.Month = historicalprices.Month";
		public static string GetHistoricalPrice_Query = @"
Select HistoricalPrices.* from HistoricalPrices
inner join symbols on symbols.id = historicalprices.SymbolID
where symbols.Symbol = @Symbol
order by Date DESC";
		public static string GetDayAggregates_Query = @"
SELECT DayAggregates.* FROM DayAggregates
INNER JOIN Symbols ON Symbols.id = DayAggregates.SymbolID
WHERE Symbols.Symbol = @Symbol";
		public static string GetDayAggregatesDayMonth_Query = @"
SELECT DayAggregates.* FROM DayAggregates
INNER JOIN Symbols ON Symbols.id = DayAggregates.SymbolID
WHERE Symbols.Symbol = @Symbol AND
day=@Day and month=@Month";
		public static string GetHistoricalPriceDayMonth_Query = @"
select * from historicalprices 
inner join symbols on symbols.id = historicalprices.SymbolID
where symbols.Symbol = @Symbol AND
day=@Day and month=@Month
ORDER BY Date DESC";
		public static string GetScreenDay_Query = @"
SELECT Symbols.Symbol,
DayAggregates.AverageGainPercentPriorClose,
DayAggregates.TotalRising+DayAggregates.TotalFalling AS 'RecordCount',
ROUND((ROUND(DayAggregates.TotalRising, 2) *100 / (DayAggregates.TotalRising + DayAggregates.TotalFalling)),2) as 'CalcRiseProb'

 FROM DayAggregates
INNER JOIN Symbols ON Symbols.id = DayAggregates.SymbolID
WHERE Symbols.Symbol IN (@SymbolList) AND
day=@Day and month=@Month
order by
--DayAggregates.AverageGainPercentPriorClose ASC
--CalcRiseProb DESC
--RecordCount DESC , CalcRiseProb DESC
--Symbols.Symbol ASC
@SortOrder
";
			public static string GetReportProbability_Query = @"
select

Symbols.Symbol,
CAST((TotalFalling+TotalRising) AS TEXT) AS 'Count',
CAST((TotalRising*100/(TotalRising+TotalFalling)) AS TEXT) AS 'Rise',
CAST(DayAggregates.AverageGainPercentPriorClose AS TEXT) AS 'Gain',
CAST(Day AS TEXT),
CAST(Month AS TEXT)

From

DayAggregates

INNER JOIN Symbols ON Symbols.id = DayAggregates.SymbolID

where

Symbols.Symbol IN (@SymbolList)

ORDER BY

Rise DESC

";
		public static string GetReportGainPercent_Query = @"
select

Symbols.Symbol,
CAST((TotalFalling+TotalRising) AS TEXT) AS 'Count',
CAST((TotalRising*100/(TotalRising+TotalFalling)) AS TEXT) AS 'Rise',
CAST(DayAggregates.AverageGainPercentPriorClose AS TEXT) AS 'Gain',
CAST(Day AS TEXT),
CAST(Month AS TEXT)

From

DayAggregates

INNER JOIN Symbols ON Symbols.id = DayAggregates.SymbolID

where

Symbols.Symbol IN (@SymbolList)

ORDER BY

Gain DESC

";
        public static string GetReport3Days_Query = @"
SELECT
Symbols.Symbol,
CAST(SUM(DayAggregates.AverageGainPriorClose) AS TEXT) AS 'Gain'

From

DayAggregates

INNER JOIN Symbols ON Symbols.id = DayAggregates.SymbolID

GROUP BY 

Symbols.Symbol 

HAVING
Symbols.Symbol IN (@SymbolList)
AND
DayAggregates.Date BETWEEN DATETIME('NOW') AND DATE('NOW','+3 day')

ORDER BY

Gain DESC

";
		public static string GetReportGain_Query = @"
select

Symbols.Symbol,
CAST((TotalFalling+TotalRising) AS TEXT) AS 'Count',
CAST((TotalRising*100/(TotalRising+TotalFalling)) AS TEXT) AS 'Rise',
CAST(DayAggregates.AverageGainPriorClose AS TEXT) AS 'Gain',
CAST(Day AS TEXT),
CAST(Month AS TEXT)

From

DayAggregates

INNER JOIN Symbols ON Symbols.id = DayAggregates.SymbolID

where

Symbols.Symbol IN (@SymbolList)

ORDER BY

Gain DESC

";
        public static string DeleteDayAggregates_Query = @"
DELETE FROM DayAggregates";
        public static string DeleteHistoricalData_Query = @"
DELETE FROM HistoricalData";
        public static string DeleteSymbols_Query = @"
DELETE FROM Symbols";

	}
}
